package db

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"reflect"
	"awesomeProject/secret"
)

type social struct {
	name string
	nums int
}

var (
	Db *sql.DB
	LinkDDL = fmt.Sprintf("%v:%v@tcp(%v:%v)/%v",secret.SJHMysql.U,secret.SJHMysql.Pwd,secret.SJHMysql.Host,secret.SJHMysql.Port,secret.SJHMysql.Db)
)
func init() {
	var err error
	Db, err = sql.Open("mysql", LinkDDL)
	if err != nil {
		panic(err)
	}
}

func Test() {
	fmt.Println(getGroup())
	n, _ := getOne()
	fmt.Println(reflect.TypeOf(n))
	fmt.Println(testCount())
	fmt.Println(testEmpty())
	fmt.Println("测试插入")
	fmt.Println(testInsert())
	fmt.Println("测试update")
	fmt.Println(testUpdate())
}

func getGroup() (qs []social, err error) {

	rows, err := Db.Query(`select name from mytest_social`)
	if err != nil {
		return
	}
	defer func() { _ = rows.Close() }()
	for rows.Next() {
		q := social{}
		err = rows.Scan(&q.name)
		if err != nil {
			return
		}
		qs = append(qs, q)

	}

	return
}

func getOne() (q social, err error) {
	q = social{}
	id := 3
	err = Db.QueryRow(`select count(1) from mytest_social where id=?`, id).Scan(&q.nums)
	return
}
func testCount() (i int, err error) {
	err = Db.QueryRow(`select count(1) from mytest_jiayou`).Scan(&i)
	return
}

func testEmpty() (i string, err error) {
	fmt.Println("测试select Empty")
	err = Db.QueryRow(`select id from mytest_jiayou where  id>10000`).Scan(&i)
	return
}

func testInsert() (err error) {
	id := 1
	_sql := `insert into test (id) values (?)`

	stmt, _ := Db.Prepare(_sql)
	defer stmt.Close()
	_, err = stmt.Exec(id)
	if err != nil {
		log.Fatal(err)
	}
	//lastId, err := res.LastInsertId()
	//if err != nil {
	//	log.Fatal(err)
	//}
	//rowCnt, err := res.RowsAffected()
	//if err != nil {
	//	log.Fatal(err)
	//}
	//fmt.Printf("ID=%d, affected=%d\n", lastId, rowCnt)

	return
}

func testUpdate() (err error) {

	_sql := `update  test set id =10 where id=11`

	stmt, _ := Db.Prepare(_sql)
	defer stmt.Close()
	res, err := stmt.Exec()
	if err != nil {
		log.Fatal(err)
	}
	n,_:=res.RowsAffected()
	fmt.Printf("afftet!%v",n)
	return
}
